﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;

namespace EffiProzDBTest
{
    [TestClass]
    public class IndexTest
    {

        SessionInterface sessionProxy;
        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_MEM;
        //String host = "localhost";
        //int port = 0;
       // String path = "Test";
        String database = "Test";
        //string sql = "";
        HsqlProperties props = new HsqlProperties();

        public IndexTest()
        {
            sessionProxy = DatabaseManager.newSession(connType, database,
                      user, password, props); 
        }




        [TestMethod]
        public void CreateIndex()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int)" ;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);         

            col1val = 1;
            col2val = "test2";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);


            sql = "CREATE INDEX " + table+"INDEX ON "+table+ " (col2)"; //single colum index
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 3;
            col2val = "test32";
            col3val = 8;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 3);


            sql = "CREATE INDEX " + table + "INDEX2 ON " + table + " (col1,col3)"; //single colum index
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 4;
            col2val = "test2";
            col3val = 9;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 4);

            //rename index
            sql = "ALTER INDEX " + table + "INDEX2 RENAME TO " + table + "INDEX2NEW;"; //single colum index
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            //drop index
            sql = "DROP INDEX "  + table + "INDEX;"; //single colum index
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            //check an insert
            col1val = 5;
            col2val = "test5";
            col3val = 6666;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 5);
           
        }

      

    }
}
